#!/usr/bin/env/python3.7
# -*- coding: utf-8 -*-

"""
@File    : server.py
@Time    : 12/12/2021
@Author  : Silence
@Version : 1.0
@Contact : silence2021silence@163.com
@License : (C)Copyright 2021-2021, Silence
@Desc    : None
"""

import datetime
import time
import pymysql

database = pymysql.connect(host="120.24.51.156", user="dormitory_system", password="123456",
                           database="dormitory_system", charset="utf8")
if database:
    print('Connect_Database_Successful')


class ReadSetting():
    def read_setting():
        cursor = database.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
                select
                    open_datetime, 
        	        late_datetime, 
        	        leaving_timeout,
                    infrared_value,
                    temperature_value_min,
                    temperature_value_max,
                    weight_value_min,
                    weight_value_max,
                    volume_value_max,
                    smoke_ttl,
                    smoke_value_max
                from
                    dormitory_system_setting
        """
        cursor.execute(sql)
        result = cursor.fetchall()
        for data in result:
            open_datetime = data["open_datetime"]
            late_datetime = data["late_datetime"]
            leaving_timeout = data["leaving_timeout"]
            infrared_value = data["infrared_value"]
            temperature_value_min = data["temperature_value_min"]
            temperature_value_max = data["temperature_value_max"]
            weight_value_min = data["weight_value_min"]
            weight_value_max = data["weight_value_max"]
            volume_value_max = data["volume_value_max"]
            smoke_ttl = data["smoke_ttl"]
            smoke_value_max = data["smoke_value_max"]
        return (open_datetime, late_datetime, leaving_timeout, infrared_value, temperature_value_min,
                temperature_value_max, weight_value_min, weight_value_max, volume_value_max, smoke_ttl,
                smoke_value_max)


class Attendance():

    # -----正常出勤----- #

    # -----从source_data中查询符合正常出勤条件的人----- #
    def action_1():
        print("正在从数据库中读取由学生端上传的源数据...")
        cursor = database.cursor()
        sql_1 = """
                select 
                    id,
                    student_number,
                    name,
                    room,
                    grade,
                    class,
                    ip_address,
                    upload_datetime
                from 
                    original_data
                where
                    upload_datetime <= '%s'
                    and upload_datetime <= '%s'
	                and infrared_value >= '%s'
	                and temperature_value > '%s'
	                and temperature_value < '%s'
	                and weight_value > '%s'
	                and weight_value < '%s';

                """
        (open_datetime, late_datetime, leaving_timeout, infrared_value, temperature_value_min,
         temperature_value_max, weight_value_min, weight_value_max, volume_value_max, smoke_ttl,
         smoke_value_max) = ReadSetting.read_setting()
        sql_2 = sql_1 % (open_datetime, late_datetime, infrared_value, temperature_value_min, temperature_value_max,
                         weight_value_min, weight_value_max)
        cursor.execute(sql_2)
        result = cursor.fetchall()
        if result == ():
            print("找不到符合'出勤'条件的学生")
        else:
            id_ = []
            student_number = []
            name = []
            room = []
            grade = []
            class_ = []
            ip_address = []
            upload_datetime = []

            for a in result:
                id_.append(a[0])
                student_number.append(a[1])
                name.append(a[2])
                room.append(a[3])
                grade.append(str(a[4]))
                class_.append(a[5])
                ip_address.append(a[6])
                upload_datetime.append(a[7])
            print("找到" + str(len(id_)) + "个符合'出勤'条件的学生")
            Attendance.action_2(id_, student_number, name, room, grade, class_, upload_datetime, ip_address)

    # -----清空旧的正常出勤的数据 #
    def action_2(id_, student_number, name, room, grade, class_, upload_datetime, ip_address):

        Attendance.action_3(id_, student_number, name, room, grade, class_, upload_datetime, ip_address)

    # -----把查询到的人写入正常出勤的数据表----- #
    def action_3(id_, student_number, name, room, grade, class_, upload_datetime, ip_address):
        print("正在把符合'出勤'条件的学生写入到'出勤数据表'...")
        cursor = database.cursor()
        sql_1 = """
                INSERT INTO `dormitory_system`.`log_data` (
	                `id`, `student_number`, `name`, `room`,
	                `grade`, `class`, `ip_address`, `status`,
	                `status_datetime`
                ) 
                VALUES 
	                (
		                '%s', '%s', '%s', '%s', '%s', '%s', 
		                '%s', '%s', '%s'
	                )
                        """

        for b in range(len(student_number)):
            sql_2 = sql_1 % ('NULL', str(student_number[b]), str(name[b]), str(room[b]),
                             str(grade[b]), str(class_[b]), str(ip_address[b]), '出勤',
                             upload_datetime[b])
            cursor.execute(sql_2)
            database.commit()
            result = cursor.fetchall()
        print("数据写入完毕")
        Attendance.action_4(id_, student_number)

    # -----删除source_data中原有的所对应的学生----- #
    def action_4(id_, student_number):
        print("正在清理源数据...")
        cursor = database.cursor()
        sql_1 = """
        DELETE FROM `dormitory_system`.`original_data` WHERE `original_data`.`id` = %s;
        """
        for c in range(len(student_number)):
            sql_2 = sql_1 % (str(id_[c]))
            cursor.execute(sql_2)
            database.commit()
            result = cursor.fetchall()
        print("源数据清理完毕")


if __name__ == "__main__":
    Attendance.action_1()